# -*- coding:utf-8 -*-

from odoo import api, fields, models, _
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT


class WTTMaterialPurchaseConsumeReportTransient(models.TransientModel):
    _name = 'material.purchase.consume.report.transient'
    _description = 'Material Purchase Consume Report Transient'

    start_datetime = fields.Datetime(string='Start Datetime', required=True)
    end_datetime = fields.Datetime(string='End Datetime', required=True)

    def action_print(self):
        return {
            'name': _('Material Purchase Consume Report'),
            'view_mode': 'tree',
            'res_model': 'material.purchase.consume.report',
            'type': 'ir.actions.act_window',
            'context': {
                'start_date': self.start_datetime.strftime(DEFAULT_SERVER_DATETIME_FORMAT),
                'end_date': self.end_datetime.strftime(DEFAULT_SERVER_DATETIME_FORMAT)
            },
        }


class WTTMaterialPurchaseConsumeReport(models.Model):
    _name = 'material.purchase.consume.report'
    _description = "Material Purchase Consume Report"
    _auto = False

    product_id = fields.Many2one('product.product', string='Product Name')
    description_eng = fields.Text(related='product_id.description_eng', string='Description Engnish')
    product_code = fields.Char(related='product_id.code', string='Product Code')
    purchase_qty = fields.Float(string='Purchase Qty')
    purchase_amount = fields.Float(string='Purchase Amount')
    consume_qty = fields.Float(string='Consume Qty')
    consume_amount = fields.Float(string='Consume Amount')
    period_end_qty = fields.Float(string='Period End Qty')
    period_end_amount = fields.Float(string='Period End Amount')
    uom_id = fields.Many2one('uom.uom', string='Uom')
    lot_id = fields.Many2one('stock.lot', string='Lots')
    currency_id = fields.Many2one('res.currency', string='Currency')

    @property
    def _table_query(self):
        start_date = self._context.get('start_date') or ''
        end_date = self._context.get('end_date') or ''
        where_pur = ''
        where_layer = ''
        if start_date and end_date:
            where_pur = f" and smp.date between '{start_date}' and '{end_date}' "
            where_layer = f" and svl.create_date <= '{end_date}' "
        select_sql = f"""
      with purchase_in as (
  select product_id,
				 lot_id,
				 sum(sum_qty) as sum_qty,
				 sum(sum_amount) as sum_amount
	  from (
		select pp.id as product_id, 
					 sml.lot_id,
					 sum(smp.quantity) sum_qty,
					 sum(smp.quantity) * coalesce(smp.price_unit, 0) as sum_amount
			from stock_move smp,
					 stock_location slf,
					 stock_location sld,
					 product_product pp,
					 stock_move_line sml
		 where smp.location_id = slf.id
			 and smp.location_dest_id = sld.id
			 and smp.product_id = pp.id
			 and smp.id = sml.move_id
			 and slf.usage = 'supplier'
			 and sld.usage = 'internal'
			 {where_pur}
		 group by pp.id, smp.price_unit, sml.lot_id
	 ) pi_inner 
	 group by product_id, lot_id
),
purchase_out as (
  select product_id,
				 lot_id,
				 sum(sum_qty) as sum_qty,
				 sum(sum_amount) as sum_amount
	  from (
		select pp.id as product_id, 
					 sml.lot_id,
					 sum(smp.quantity) sum_qty,
					 sum(smp.quantity) * coalesce(smp.price_unit, 0) as sum_amount
			from stock_move smp,
					 stock_location slf,
					 stock_location sld,
					 product_product pp,
					 stock_move_line sml
		 where smp.location_id = slf.id
			 and smp.location_dest_id = sld.id
			 and smp.product_id = pp.id
			 and smp.id = sml.move_id
			 and slf.usage = 'internal'
			 and sld.usage = 'supplier'
			 {where_pur}
		 group by pp.id, smp.price_unit, sml.lot_id
	  ) po_inner
		group by product_id, lot_id
),
purchase as (
	select coalesce(pi.product_id, po.product_id) as product_id,
				 coalesce(pi.lot_id, po.lot_id) as lot_id,
				 coalesce(pi.sum_qty, 0) - coalesce(po.sum_qty, 0) as sum_qty,
				 coalesce(pi.sum_amount, 0) - coalesce(po.sum_amount, 0) as sum_amount
	  from purchase_in pi
		full join purchase_out po on 
			pi.product_id = po.product_id
	   and pi.lot_id = po.lot_id
),
consume_in as (
	select product_id,
				 lot_id,
				 sum(sum_qty) as sum_qty,
				 sum(sum_amount) as sum_amount
	  from (
		select pp.id as product_id, 
					 sml.lot_id,
					 sum(smp.quantity) sum_qty,
					 sum(smp.quantity) * coalesce(svl.unit_cost, 0) as sum_amount
			from stock_move smp
			left join stock_valuation_layer svl on smp.id = svl.stock_move_id,
					 stock_location slf,
					 stock_location sld,
					 product_product pp,
					 stock_move_line sml
		 where smp.location_id = slf.id
			 and smp.location_dest_id = sld.id
			 and smp.product_id = pp.id
			 and smp.id = sml.move_id
			 and smp.id = svl.stock_move_id
			 and slf.usage = 'internal'
			 and sld.usage in ('inventory', 'production')
			 {where_pur}
		 group by pp.id, svl.unit_cost, sml.lot_id
	 ) pi_inner 
	 group by product_id, lot_id
),
consume_out as (
	select product_id,
				 lot_id,
				 sum(sum_qty) as sum_qty,
				 sum(sum_amount) as sum_amount
	  from (
		select pp.id as product_id, 
					 sml.lot_id,
					 sum(smp.quantity) sum_qty,
					 sum(smp.quantity) * coalesce(svl.unit_cost, 0) as sum_amount
			from stock_move smp
			left join stock_valuation_layer svl on smp.id = svl.stock_move_id,
					 stock_location slf,
					 stock_location sld,
					 product_product pp,
					 stock_move_line sml
		 where smp.location_id = slf.id
			 and smp.location_dest_id = sld.id
			 and smp.product_id = pp.id
			 and smp.id = sml.move_id
			 and smp.id = svl.stock_move_id
			 and slf.usage in ('inventory', 'production')
			 and sld.usage = 'internal'
			 {where_pur}
		 group by pp.id, svl.unit_cost, sml.lot_id
	 ) pi_inner 
	 group by product_id, lot_id
),
consume as (
	select coalesce(ci.product_id, co.product_id) as product_id,
				 coalesce(ci.lot_id, co.lot_id) as lot_id,
				 coalesce(ci.sum_qty, 0) - coalesce(co.sum_qty, 0) as sum_qty,
				 coalesce(ci.sum_amount, 0) - coalesce(co.sum_amount, 0) as sum_amount
	  from consume_in ci
		full join consume_out co on 
			ci.product_id = co.product_id
	   and ci.lot_id = co.lot_id
),
product as (
	select pp.id,
				 pt.uom_id
	  from product_product pp,
		   product_template pt,
		   product_category pc
	 where pp.product_tmpl_id = pt.id
	   and pt.categ_id = pc.id
	   and pt.type = 'product'
	   and pc.code not in ('CHP', 'FYL', 'SBZC', 'WWJG', 'BCP')
),
layer_line as (
	select svlo.product_id, 
				 sum(svlo.remaining_qty) as sum_qty,
				 case when sum(coalesce(svli.sum_qty, 0)) = 0 then 0
						  else sum(coalesce(svli.sum_value, 0)) / sum(coalesce(svli.sum_qty, 0))
				 end avg_cost
	  from stock_valuation_layer svlo,
		     (
		     select max(svl.create_date) as create_date,
							  sum(coalesce(svl.value, 0)) as sum_value,
								sum(coalesce(svl.quantity, 0)) as sum_qty,
							  svl.product_id
				   from stock_valuation_layer svl
				  where 1 = 1 
				    {where_layer}
					group by svl.product_id
				 ) svli
	 where svlo.product_id = svli.product_id
	   and svlo.create_date = svli.create_date
	 group by svlo.product_id
),
currency as (
    select id from res_currency where name = 'THB'
),

main as (
	select pp.id as product_id,
				 coalesce(p_pur.lot_id, p_con.lot_id) as lot_id,
				 p_pur.sum_qty as purchase_qty,
				 p_pur.sum_amount as purchase_amount,
				 p_con.sum_qty as consume_qty,
				 p_con.sum_amount as consume_amount,
				 ll.sum_qty as period_end_qty,
				 ll.sum_qty * ll.avg_cost as period_end_amount,
				 pp.uom_id
    from product pp
		left join purchase p_pur on pp.id = p_pur.product_id
		left join consume p_con on pp.id = p_con.product_id
		left join layer_line ll on pp.id = ll.product_id
)

select row_number() OVER() AS id,
			 product_id,
			 coalesce(purchase_qty, 0) as purchase_qty,
			 coalesce(purchase_amount, 0) as purchase_amount,
			 coalesce(consume_qty, 0) as consume_qty,
			 coalesce(consume_amount, 0) as consume_amount,
			 coalesce(period_end_qty, 0) as period_end_qty,
			 coalesce(period_end_amount, 0) as period_end_amount,
			 uom_id,
			 lot_id,
			 (select id from currency) as currency_id
  from main
 where (purchase_qty is not null 
    or consume_qty is not null)
	 and (purchase_qty != 0
	  or consume_qty != 0)
        """
        return select_sql
